﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Transactions;

namespace IntellectShop.Common
{
	public partial class Software : Commodity
	{
		public static List<Common.Category> RetrieveCategoriesAll()
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var query =
							from category in context.Categories
							join software in context.Softwares
								on category.CategoryID equals software.CategoryID
							group category by new
							{
								category.CategoryID,
								category.SortingID,
								category.Name
							} into g
							orderby g.Key.SortingID descending, g.Key.Name
							select g.Key;

				List<Common.Category> result = new List<Category>();
				foreach (var row in query)
				{
					result.Add(new Common.Category
					{
						CategoryID = row.CategoryID,
						Name = row.Name,
						SortingID = row.SortingID
					});
				}

				return result;
			}
		}

		public static Software RetrieveBySoftwareID(int softwareID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from software in dataContext.Softwares
							where software.SoftwareID == softwareID
							join commodity in dataContext.Commodities
								on software.CommodityID equals commodity.CommodityID
							join category in dataContext.Categories
								on software.CategoryID equals category.CategoryID
							orderby commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								software.SoftwareID,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							}).SingleOrDefault();

				if (query == null)
					return null;

				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName };

				Common.Software selectedSoftware = new Common.Software
				{
					SoftwareID = query.SoftwareID,
					Category = selectedCategory,
					Code = query.Code,
					CommodityID = query.CommodityID,
					SortingID = query.SortingID,
					Description = query.Descr,
					Name = query.Name,
					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }
				};

				return selectedSoftware;
			}
		}

		public static Software RetrieveByCommodityID(int commodityID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from commodity in dataContext.Commodities
							where commodity.CommodityID == commodityID
							join software in dataContext.Softwares
								on commodity.CommodityID equals software.CommodityID
							join category in dataContext.Categories
								on software.CategoryID equals category.CategoryID
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								CategorySortingID = category.SortingID,
								software.SoftwareID,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							}).SingleOrDefault();

				if (query == null)
					return null;

				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName, SortingID = query.CategorySortingID };

				Common.Software selectedSoftware = new Common.Software
				{
					SoftwareID = query.SoftwareID,
					Category = selectedCategory,
					Code = query.Code,
					CommodityID = query.CommodityID,
					SortingID = query.SortingID,
					Description = query.Descr,
					Name = query.Name,
					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB }
				};

				return selectedSoftware;
			}
		}

		public new static Dictionary<Common.Category, List<Common.Software>> RetrieveAll()
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from commodity in dataContext.Commodities
							join software in dataContext.Softwares
								on commodity.CommodityID equals software.CommodityID
							join category in dataContext.Categories
								on software.CategoryID equals category.CategoryID
							orderby category.SortingID descending, commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								CategorySortingID = category.SortingID,
								software.SoftwareID,
								commodity.CommodityID,
								CommoditySortingID = commodity.SortingID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							});

				Dictionary<Common.Category, List<Common.Software>> result = new Dictionary<Common.Category, List<Common.Software>>();
				Common.Category selectedCategory = null;
				foreach (var row in query)
				{
					selectedCategory = new Common.Category { CategoryID = row.CategoryID, Name = row.CategoryName, SortingID = row.CategorySortingID };
					List<Common.Software> softwares;
					if (result.TryGetValue(selectedCategory, out softwares))
					{
						if (softwares == null)
						{
							softwares = new List<Software>();
						}
					}
					else
					{
						softwares = new List<Software>();
						result.Add(selectedCategory, softwares);
					}

					softwares.Add(new Common.Software
					{
						SoftwareID = row.SoftwareID,
						Category = selectedCategory,
						Code = row.Code,
						CommodityID = row.CommodityID,
						SortingID = row.CommoditySortingID,
						Name = row.Name,
						Price = new Common.Money { Amount = (row.Price.HasValue ? row.Price.Value : 0), Currency = Currency.RUB }
					});
				}

				return result;
			}
		}

		public static List<Common.Software> RetrieveByCategoryID(int categoryID)
		{
			List<Common.Software> selectedSoftwares = new List<Common.Software>();
			Common.Category selectedCategory = Common.Category.RetrieveByID(categoryID);
			if (selectedCategory == null)
			{
				return selectedSoftwares;
			}

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var querySoftwares =
							from category in dataContext.Categories
							where category.CategoryID == categoryID
							join software in dataContext.Softwares
								on category.CategoryID equals software.CategoryID
							join commodity in dataContext.Commodities
								on software.CommodityID equals commodity.CommodityID
							orderby commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								software.SoftwareID,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							};

				foreach (var each in querySoftwares)
				{
					selectedSoftwares.Add(new Common.Software
					{
						SoftwareID = each.SoftwareID,
						Category = selectedCategory,
						Code = each.Code,
						CommodityID = each.CommodityID,
						SortingID = each.SortingID,
						Description = each.Descr,
						Name = each.Name,
						Price = new Common.Money { Amount = (each.Price.HasValue ? each.Price.Value : 0), Currency = Currency.RUB }
					});
				}

				return selectedSoftwares;
			}
		}

		public new void Create(string createdBy)
		{
			using (TransactionScope trans = new TransactionScope())
			{
				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
				{
					var commodityDB = new DB.Commodity
						{
							Code = this.Code,
							Descr = this.Description,
							Name = this.Name,
							Price = this.Price.Amount,
							SortingID = this.SortingID,
							Created = DateTime.Now,
							CreatedBy = createdBy,
							CommodityType = (int)this.CommodityType
						};
					dataContext.Commodities.InsertOnSubmit(commodityDB);
					dataContext.SubmitChanges();

					var softwareDB = new DB.Software
						{
							CommodityID = commodityDB.CommodityID,
							CategoryID = this.Category.CategoryID,
							Created = DateTime.Now,
							CreatedBy = createdBy
						};
					dataContext.Softwares.InsertOnSubmit(softwareDB);
					dataContext.SubmitChanges();
					this.SoftwareID = softwareDB.SoftwareID;
					this.CommodityID = commodityDB.CommodityID;

					trans.Complete();
				}
			}

			this.Tags.CommodityID = this.CommodityID;
			this.Tags.Save();
		}

		public new void Update(string updateBy)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var softwareDB = dataContext.Softwares.SingleOrDefault(x => x.CommodityID == this.CommodityID);
				softwareDB.CategoryID = this.Category.CategoryID;
				softwareDB.Updated = DateTime.Now;
				softwareDB.UpdatedBy = updateBy;

				softwareDB.Commodity.SortingID = this.SortingID;
				softwareDB.Commodity.Name = this.Name;
				softwareDB.Commodity.Code = this.Code;
				softwareDB.Commodity.Descr = this.Description;
				softwareDB.Commodity.Price = this.Price.Amount;
				softwareDB.Commodity.Updated = DateTime.Now;
				softwareDB.Commodity.UpdatedBy = updateBy;

				dataContext.SubmitChanges();
			}

			this.Tags.Save();
		}

		public new static void Delete(int softwareID)
		{
			int commodityID;
			using (TransactionScope trans = new TransactionScope())
			{
				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
				{
					var softwareDB = (from software in dataContext.Softwares where software.SoftwareID == softwareID select software).SingleOrDefault();
					commodityID = softwareDB.CommodityID;

					var cartItemsDB = from cart in dataContext.CartItems where cart.CommodityID == commodityID select cart;
					dataContext.CartItems.DeleteAllOnSubmit(cartItemsDB);
					dataContext.SubmitChanges();

					var tagsSetDB = from tags in dataContext.TagsSets where tags.CommodityID == commodityID select tags;
					dataContext.TagsSets.DeleteAllOnSubmit(tagsSetDB);
					dataContext.SubmitChanges();

					dataContext.Softwares.DeleteOnSubmit(softwareDB);
					dataContext.SubmitChanges();

					var commodityDB = (from commodity in dataContext.Commodities where commodity.CommodityID == commodityID select commodity).SingleOrDefault();
					dataContext.Commodities.DeleteOnSubmit(commodityDB);
					dataContext.SubmitChanges();

					trans.Complete();
				}
			}
		}
	}
}
